import json
from openpyxl import Workbook
from datetime import datetime

from pathlib import Path
import os
import sys
import site

# 获取当前Python解释器的安装路径
python_path = sys.executable

# 获取当前虚拟环境的lib目录路径
venv_lib_dir = next(p for p in sys.path if p.startswith(site.getsitepackages()[0]) and 'lib' in p)

# 获取虚拟环境的地址
venv_address = venv_lib_dir.replace('lib', '')

print(f"当前虚拟环境的地址是: {venv_address}")
# root_path = Path(__file__).resolve().parent.parent
if os.name == 'nt':
    root_path = os.path.dirname(os.path.realpath(sys.argv[0]))+'\\template\\'
else:
    root_path = '/home/project_python/file'
print(root_path)


def read_file(tmpfile):
    content = ''
    with open(tmpfile, 'r', encoding='utf-8') as file:
        # content = file.read()  # 一次性读取整个文件内容
        # 或者使用以下方式逐行读取
        for line in file:
            content += line
        # print(content)
    return content

def json_to_excel(jsonstr, sheet,i,j):
    i=1
    for data_pro in jsonstr:
        if type(jsonstr[data_pro]) == list:
            skulist = jsonstr[data_pro]
            for sku in skulist:
                m =1
                for sku_pro in sku:
                    if j == 1:
                        sheet.cell( j, i , sku_pro)
                        sheet.cell(1+j, i , sku[sku_pro])
                        j=j+1
                    else:
                        sheet.cell(j, i, sku[sku_pro])
                    m = m + 1
                    i = i + m
                j = j + 1
        else:
            i+=1
            if j == 1:
                sheet.cell( j, i , data_pro)
                sheet.cell( 1+ j, i , jsonstr[data_pro])
                j = j+1
            else:
                sheet.cell(j , i , jsonstr[data_pro])
    j = j + 1
    return i,j


def json_to_json(jsonstr, bill_no):
    # 加载现有的Excel文件
    workbook = Workbook()
    # 选择工作表
    sheet = workbook.active
    i = 0
    j = 1
    print(type(jsonstr))
    if type(jsonstr) == str:
        content = json.loads(jsonstr)
    else:
        content = jsonstr
    if 'data' in content:
        content = content['data']
    # print(content)
    if content is None:
        return
    if type(content) == list:
       for item in content:
           i,j=json_to_excel(item,sheet,i,j)
           print(i,j)
    else:
        json_to_excel(content, sheet,i,j)
    file_time = datetime.now().strftime('%Y%m%d_%H%M%S')
    filename =str(root_path)+file_time + "_" + bill_no + "_" + 'json2excel.xlsx'
    workbook.save(filename)
    workbook.close()
    return filename

file = "d:\\tmp\\tmp.txt"
content = read_file(file)
json_to_json(content,"aa20241104")